﻿using System;
using System.Collections.Generic;
using System.Windows.Forms;
using System.IO;
using System.Windows.Media;
using System.Linq;

using PublicTemplateEditor.Helper;
using PublicTemplateEditor.Mold;
using System.Text;
using System.Windows.Media.Imaging;

namespace PublicTemplateEditor.DataAccess
{
    public class FileDA
    {
        #region sql语句
        private const string SaveImageSql = "insert into m_file (type,src,thumbimg,oldimg,editImg,name)values({0},'{1}',@thumbData,@oldData,@editData,'{2}');";
        
        private const string SaveMp3Sql = "insert into m_file (type,src,oldimg,name)values({0},'{1}',@oldData,'{2}');";
        
        private const string GetMaterialsSql = "select name,type,thumbImg,src,tableConfig  from (select name,type,thumbImg,src,tableConfig from m_file where type in ({0}) order by intime desc ) limit {1} offset {2} ";
        
        private const string GetAllMaterialsSql = "select name,type from m_file";
        
        private const string GetAllMaterialsNameSql = "select name from m_file";

        private const string GetFileInfoListSql = "select distinct name,oldimg,type from m_file  where  name in ({0})";
        #endregion

        #region 素材存
        /// <summary>
        /// 存素材到数据库，返回缩略图的ImageBrush
        /// </summary>
        /// <param name="path">素材路径</param>
        /// <param name="imgType">所属类型</param>
        /// <param name="IsNewID">true：采用新的guid false：采用文件名</param>
        /// <returns></returns>
        public static void SaveImage(string path, EditTypes imgType, bool IsNewID)
        {
            double width;
            double height;
            if (!File.Exists(path))
                return;

            #region 检查文件尺寸
            if (imgType == EditTypes.BackgroundImage)
            {
                if (!CommonHelper.CheckSize(path, 840, 560))
                    return;
            }

            if (imgType == EditTypes.TemplateBackground)
            {
                if (!CommonHelper.CheckSize(path, 1024, 768))
                    return;
            }

            #endregion    

            #region 定义
            Dictionary<string, byte[]> pars = new Dictionary<string, byte[]>();
            string sql = string.Empty;
            #endregion

            #region  取缩略图，生成sql，并转化为ImageBrush
            using (ThumbnailsHelper thumb = new ThumbnailsHelper(path))
            {
                if (imgType == EditTypes.BackgroundImage)
                {
                    thumb.Height = 52;
                    thumb.Width = 78;
                }
                else
                {
                    thumb.Height = 52;
                    thumb.Width = 52;
                }

                byte[] thumbArray = thumb.GenerateFromPath(path);
                pars.Add("thumbData", thumbArray);
            }
            #endregion

            #region  存原图
            if (imgType != EditTypes.Gif)
            {
                using (ThumbnailsHelper thumb = new ThumbnailsHelper())
                {

                    CommonHelper.GetImageSize(path,out width,out height);
                    //如果尺寸太大，则缩小
                    if (width >= 840 && height >= 560)
                    {
                        thumb.Height = 840;
                        thumb.Width = 560;
                    }
                    else
                    {
                        thumb.Height = (int)(height);
                        thumb.Width = (int)(width);
                    }

                    pars.Add("oldData", thumb.GenerateFromPath(path));
                }
            }
            else
                pars.Add("oldData", File.ReadAllBytes(path));
            #endregion

            #region 存编辑图
            if (imgType == EditTypes.BackgroundImage || imgType == EditTypes.TemplateBackground)
            {
                MemoryStream ms = new MemoryStream();
                using (ThumbnailsHelper thumb = new ThumbnailsHelper() { Width = 840, Height = 560, ImgPath = path })
                {
                    pars.Add("editData", thumb.GenerateFromPath(path));
                    ms.Close();
                }
            }
            else
                pars.Add("editData", null);
            #endregion

            sql = string.Format(SaveImageSql, (int)imgType, path, IsNewID ? CommonHelper.GetGuid() + Path.GetExtension(path): Path.GetFileName(path));
            CommonAccess.sqlite.ExeSql(sql, pars);
        }

        /// <summary>
        /// 下载素材到数据库，返回缩略图的ImageBrush
        /// </summary>
        /// <param name="imgByteArray">素材二进制流</param>
        /// <param name="fileName">素材名称,为了取扩展名</param>
        /// <param name="imgType">所属类型</param>
        /// <param name="IsCheck">是否需要检查存在</param>
        /// <returns></returns>
        public static ImageBrush SaveImage(byte[] imgByteArray, string fileName, EditTypes imgType, bool IsCheck)
        {
            #region 检测
            if (imgByteArray == null) return null;
            if (imgByteArray.Length == 0)    return null;

            if (IsCheck &&  FileDA.FileExit(fileName))
            {
                MessageBox.Show("该文件已经下载过");
                return null;
            }
            #endregion

            #region 定义
            ImageBrush brush = new ImageBrush();
            Dictionary<string, byte[]> pars = new Dictionary<string, byte[]>();
            string sqlFormat =
                "delete from m_file where name='{2}';insert into m_file (type,src,thumbimg,oldimg,editImg,name)values({0},'{1}',@thumbData,@oldData,@editData,'{2}');";

            string sql = string.Empty;

            #endregion

            #region  取缩略图，生成sql，并转化为ImageBrush
            using (ThumbnailsHelper thumb = new ThumbnailsHelper())
            {
                if (imgType == EditTypes.BackgroundImage)
                {
                    thumb.Height = 52;
                    thumb.Width = 78;
                }
                else
                {
                    thumb.Height = 52;
                    thumb.Width = 52;
                }


                byte[] byteArray = thumb.GenerateFromByte(imgByteArray, Path.GetExtension(fileName));
                pars.Add("thumbData", byteArray);
                brush = CommonHelper.GetImageBrush(byteArray);
            }
            #endregion

            #region  存原图
            pars.Add("oldData", imgByteArray);
            #endregion

            #region 存编辑图
            if (imgType == EditTypes.BackgroundImage || imgType == EditTypes.TemplateBackground)
            {
                MemoryStream ms = new MemoryStream();
                using (ThumbnailsHelper thumb = new ThumbnailsHelper(840, 560))
                {
                    pars.Add("editData", thumb.GenerateFromByte(imgByteArray, Path.GetExtension(fileName)));
                    ms.Close();
                }
            }
            else
                pars.Add("editData", null);
            #endregion

            sql = string.Format(sqlFormat, (int)imgType, fileName, fileName);
            CommonAccess.sqlite.ExeSql(sql, pars);
            return brush;
        }

        /// <summary>
        /// 截图 作为小图标存入素材库 
        /// </summary>
        /// <param name="source">剪贴板取到的数据</param>
        /// <returns>小图标的配置</returns>
        public static string SaveImage(BitmapSource source)
        {
            #region 取 byte[]
            byte[] byteArray = null;
            using (MemoryStream ms = new MemoryStream())
            {
                source.Save(ms, myImageFormat.Png);
                byteArray = ms.ToArray();
            }
            #endregion

            #region 定义
            Dictionary<string, byte[]> pars = new Dictionary<string, byte[]>();
            string sqlFormat =
                "insert into m_file (type,src,thumbimg,oldimg,editImg,name)values({0},'{1}',@thumbData,@oldData,@editData,'{2}');";
            string fileName = CommonHelper.GetGuid() + ".png";
            string sql = string.Empty;
            #endregion

            #region  取缩略图，生成sql
            using (ThumbnailsHelper thumb = new ThumbnailsHelper(52, 52))
            {
                byte[] thumbArray = thumb.GenerateFromByte(byteArray, Path.GetExtension(fileName));
                pars.Add("thumbData", thumbArray);
            }
            #endregion

            #region  存原图
            pars.Add("oldData", byteArray);
            pars.Add("editData", null);
            #endregion

            #region 存数据库
            sql = string.Format(sqlFormat, (int)EditTypes.Icon, fileName, fileName);
            CommonAccess.sqlite.ExeSql(sql, pars);
            #endregion

            #region 生成配置
            EditObject eo = new EditObject(EditTypes.Icon);
            eo.Width = source.Width;
            eo.Height = source.Height;
            eo.CenterPoint = new System.Windows.Point(eo.Width / 2, eo.Height / 2);
            IconInfo icon = new IconInfo() { SourceName = fileName };
            eo.Obj = icon;
            return eo.ConfigStr;
            #endregion

        }

        /// <summary>
        /// 素材是否已经存在
        /// </summary>
        /// <param name="sid">网络ID</param>
        /// <param name="type">类型</param>
        /// <returns></returns>
        public static bool FileExit(string id)
        {
            string sql = " select count(1) from m_file where name='{0}'";
            sql = string.Format(sql, id);

            int count = CommonHelper.ConvertToInt(CommonAccess.sqlite.GetScalar(sql));
            if (count > 0)
                return true;
            else
                return false;
        }

        #endregion

        #region MP3存
        /// <summary>
        /// 本地添加MP3
        /// </summary>
        /// <param name="path">素材路径</param>
        /// <param name="imgType">所属类型</param>
        /// <param name="IsNewID">true:采用新的GUID false:文件名</param>
        /// <returns></returns>
        public static void SaveMp3(string path,bool IsNewID)
        {
            if (!File.Exists(path))
            {
                MessageBox.Show("文件不存在");
                return;
            }

            Dictionary<string, byte[]> pars = new Dictionary<string, byte[]>();
            string sql = string.Empty;
            pars.Add("oldData", File.ReadAllBytes(path));
            sql = string.Format(SaveMp3Sql, (int)EditTypes.TemplateMusic, Path.GetFileNameWithoutExtension(path),IsNewID ? CommonHelper.GetGuid() + Path.GetExtension(path):Path.GetFileName(path));
            CommonAccess.sqlite.ExeSql(sql, pars);
        }

        /// <summary>
        ///  下载音乐
        /// </summary>
        /// <param name="path">素材路径</param>
        /// <param name="fileName">文件名称</param>
        /// <returns></returns>
        public static void SaveMp3(byte[] Data, string fileName)
        {
            if (Data == null) return;
            if (Data.Length == 0) return;

            Dictionary<string, byte[]> pars = new Dictionary<string, byte[]>();
            string sql = string.Empty;
            pars.Add("oldData", Data);
            sql = string.Format(SaveMp3Sql, (int)EditTypes.TemplateMusic, fileName, fileName);
            CommonAccess.sqlite.ExeSql(sql, pars);
        }

        #endregion

        #region 表格存

        /// <summary>
        /// 表格存储
        /// </summary>
        /// <param name="imgByteArray">表格截图二进制流</param>
        /// <param name="fileName">GUID</param>
        /// <param name="tableConfig">表格配置</param>
        /// <param name="tableName">表格名字，用户自定义的</param>
        public static void SaveTable(byte[] imgByteArray, string fileName, string tableConfig,string tableName)
        {
            if (imgByteArray == null) return;
            if (imgByteArray.Length == 0)  return;

            if (FileDA.FileExit(fileName))
            {
                MessageBox.Show("该文件已经存在");
                return;
            }

            #region 定义
            ImageBrush brush = new ImageBrush();
            Dictionary<string, byte[]> pars = new Dictionary<string, byte[]>();
            string sqlFormat =
                    "insert into m_file (type,thumbimg,oldimg,name,tableConfig,src)values({0},@thumbData,@oldData,'{1}','{2}','{3}');";

            string sql = string.Empty;

            #endregion

            #region  取缩略图，生成sql，并转化为ImageBrush
            using (ThumbnailsHelper thumb = new ThumbnailsHelper())
            {
                thumb.Height = 52;
                thumb.Width = 52;

                byte[] byteArray = thumb.GenerateFromByte(imgByteArray, ".png");
                pars.Add("thumbData", byteArray);
            }
            #endregion

            #region  存原图
            pars.Add("oldData", imgByteArray);
            #endregion

            sql = string.Format(sqlFormat, (int)EditTypes.Table, fileName, tableConfig,tableName);
            CommonAccess.sqlite.ExeSql(sql, pars);
        }

        #endregion

        #region 混合素材存储
        /// <summary>
        /// 混合素材存储
        /// </summary>
        /// <param name="imgByteArray">截图二进制流</param>
        /// <param name="fileName">GUID</param>
        /// <param name="tableConfig">配置</param>
        /// <param name="tableName">名字，用户自定义的</param>
        public static void SaveCombo(byte[] imgByteArray, string fileName, string  config, string name)
        {
            if (imgByteArray == null) return;
            if (imgByteArray.Length == 0) return;

            if (FileDA.FileExit(fileName))
            {
                MessageBox.Show("该文件已经存在");
                return;
            }

            #region 定义
            ImageBrush brush = new ImageBrush();
            Dictionary<string, byte[]> pars = new Dictionary<string, byte[]>();
            string sqlFormat =
                    "insert into m_file (type,thumbimg,oldimg,name,tableConfig,src)values({0},@thumbData,@oldData,'{1}','{2}','{3}');";

            string sql = string.Empty;

            #endregion

            #region  取缩略图，生成sql，并转化为ImageBrush
            using (ThumbnailsHelper thumb = new ThumbnailsHelper())
            {
                thumb.Height = 52;
                thumb.Width = 52;

                byte[] byteArray = thumb.GenerateFromByte(imgByteArray, ".png");
                pars.Add("thumbData", byteArray);
            }
            #endregion

            #region  存原图
            pars.Add("oldData", imgByteArray);
            #endregion

            sql = string.Format(sqlFormat, (int)EditTypes.Combo, fileName, config, name);
            CommonAccess.sqlite.ExeSql(sql, pars);
        }

        #endregion

        #region 素材--取
        /// <summary>
        /// 取出某一类型的缩略图,对象类型
        /// </summary>
        /// <param name="type"></param>
        /// <param name="limit">返回行数</param>
        /// <param name="offset">前面的条数</param>
        /// <returns></returns>
        public static Dictionary<string, MaterialInfo> GetMaterials(EditTypes type, int limit, int offset)
        {
            string sql = string.Format(GetMaterialsSql, (int)type, limit, offset);
            
            List<object[]> objList = CommonAccess.sqlite.GetArray(sql);

            Dictionary<string, MaterialInfo> brushDict = new Dictionary<string, MaterialInfo>();
            if (objList == null)
                return brushDict;
             
            MaterialInfo material = null;
            foreach (object[] obj in objList)
            {
                material = new MaterialInfo();
                material.Name = obj[0].ToString();
                material.Type = (EditTypes)CommonHelper.ConvertToInt(obj[1]);
                material.Src =  obj[3].ToString().Decode();
                material.Config = obj[4].ToString();
                if (type != EditTypes.TemplateMusic)
                    material.Source =  CommonHelper.ConvertToByte(obj[2]).ToImageSource();

                brushDict.Add(material.Name.ToString(), material);
            }
            return brushDict;
        }

        /// <summary>
        /// 获取所有的素材 ,仅包含素材名,逗号分隔
        /// </summary>
        /// <returns></returns>
        public static string GetAllMaterailNames()
        {
            List<object[]> objList = CommonAccess.sqlite.GetArray(GetAllMaterialsNameSql);
            if (objList == null) return string.Empty;
            var str = objList.Select(o => o[0].ToString()).ToArray();
            var ret=string.Join(",", str);
            return ret;
            //StringBuilder sb = new StringBuilder(); 
            //foreach (object[] obj in objList)
            //{
            //    sb.Append(obj[0].ToString() + ",");
            //}
            //if (sb.Length == 0)
            //    return string.Empty;
           // return sb.ToString().Substring(0, sb.Length - 1);
        }

        /// <summary>
        /// 某一类型缩略图总数，便于分页计算
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public static int GetThumbailsCount(EditTypes type)
        {
            int total = 0;
            string sql = "select count(1) from m_file where type={0} ";
            sql = string.Format(sql, (int)type);

            total = CommonAccess.sqlite.GetScalar(sql).ToInt();
            return total;
        }

        /// <summary>
        /// 获取素材ImageBrush
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static ImageBrush GetImage(string name, ImageTypes type)
        {
            ImageBrush brush = null;
            string sql = string.Empty;
            if (type == ImageTypes.Edit)
                sql = "select editImg from m_file where name='{0}'";
            else if (type == ImageTypes.Original)
                sql = "select oldImg from m_file where name='{0}'";

            sql = string.Format(sql, name);
            object obj = CommonAccess.sqlite.GetScalar(sql);
            if (obj == null) return null;
             
            brush = CommonHelper.ConvertToByte(obj).ToImageBrush();
            brush.Stretch = Stretch.None;
            return brush;
        }

        /// <summary>
        /// 获取编辑的素材ImageSource
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static ImageSource GetImageSource(string name, ImageTypes type)
        {
            ImageSource source = null;
            string sql = string.Empty;

            if (type == ImageTypes.Edit)
                sql = "select editImg from m_file where name='{0}'";
            else if (type == ImageTypes.Original)
                sql = "select oldImg from m_file where name='{0}'";

            sql = string.Format(sql, name);
            object obj = CommonAccess.sqlite.GetScalar(sql);
            if (obj == null) return null;
            source = CommonHelper.ConvertToByte(obj).ToImageSource();
            return source;
        }

        /// <summary>
        /// 获取素材的二进制流
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static byte[] GetImageByte(string name, ImageTypes type)
        {
            string sql = string.Empty;

            switch (type)
            {
                case ImageTypes.Thumbnails:
                    {
                        sql = "select thumbImg from m_file where name='{0}'";
                        break;
                    }
                case ImageTypes.Original:
                    {
                        sql = "select oldImg from m_file where name='{0}'";
                        break;
                    }
                case ImageTypes.Edit:
                    {
                        sql = "select editImg from m_file where name='{0}'";
                        break;
                    }
                default: break;
            }
            sql = string.Format(sql, name);
            object obj = CommonAccess.sqlite.GetScalar(sql);
            if (obj == null) return null;
             
            return obj.ToByte();
        }

        /// <summary>
        /// MP3
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static MaterialInfo GetMp3(string name)
        {
            string sqlFormat = "select name,src,oldimg from m_file where name='{0}'";
            MaterialInfo materail = null;
            
            object[] objList = CommonAccess.sqlite.GetRow(string.Format(sqlFormat, name));
            if (objList == null) return null;

            materail = new MaterialInfo()
            {
                Name = objList[0].ToString(),
                Src = objList[1].ToString(),
                Data = CommonHelper.ConvertToByte(objList[2])
            };
         
            return materail;
        }

        /// <summary>
        /// 获取素材信息
        /// </summary>
        /// <param name="name">文件名串，以逗号分隔</param>
        /// <returns></returns>
        public static List<TempFileInfo> GetFileInfoList(string name)
        {
            //"select name,oldimg,type from m_file  where  name='{0}'"
            List<object[]> objList = CommonAccess.sqlite.GetArray(string.Format(GetFileInfoListSql, name));
            if (objList == null) return null;

            var ret = objList.Select(o => new TempFileInfo()
            {
                FileName = o[0].ToString(),
                Data = CommonHelper.ConvertToByte(o[1]),
                FileType = CommonHelper.ConvertToInt(o[2]),
            }).ToList();

            //List<TempFileInfo> fileList = new List<TempFileInfo>();
            //foreach (object[] obj in objList)
            //{
            //    fileList.Add(new TempFileInfo()
            //    {
            //        FileName = obj[0].ToString(),
            //        Data = CommonHelper.ConvertToByte(obj[1]),
            //        FileType = CommonHelper.ConvertToInt(obj[2]),
            //    });
            //}

            //return fileList;
            return ret;
        }
        #endregion

        #region 素材--删
        //删除素材
        public static void DeleteMaterials(MaterialInfo matetail)
        {
            if (CheckUsed(matetail.Name))
            {
                MessageBox.Show("该素材已被使用，不能删除！");
                return;
            }
            string sql = "delete from m_file where name = '{0}'";

            sql = string.Format(sql, matetail.Name);

            CommonAccess.sqlite.ExeSql(sql);
        }

        /// <summary>
        /// 删除素材
        /// </summary>
        /// <param name="id"></param>
        public static void DeleteMaterialsNoCheck(string id)
        {
            string sql = "delete from m_file where name = '{0}'";
            sql = string.Format(sql, id);
            CommonAccess.sqlite.ExeSql(sql);
        }

        #endregion

        #region 检测素材是否已被使用
        /// <summary>
        /// 检测素材是否已被使用
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static bool CheckUsed(string fileName)
        {
            string ecfg = string.Empty;

            #region 在元素表里面检索
            List<object[]> objList = CommonAccess.sqlite.GetArray("select ecfg from m_element ");

            var ret = objList.Any(o => o[0].ToString().IndexOf(fileName)!=-1);
            if (ret == true)
                return ret;

            //if (objList != null)
            //{
            //    foreach (object[] obj in objList)
            //    {
            //        ecfg = obj[0].ToString();
            //        if (ecfg.IndexOf(fileName) != -1)
            //        {
            //            return true;
            //        }
            //    }
            //}
            #endregion

            #region 页面表里面检索
            objList = CommonAccess.sqlite.GetArray("select pbg from m_page;");

            ret = objList.Any(o => o[0].ToString().IndexOf(fileName) != -1);
            if (ret == true)
                return ret;

            //if (objList != null)
            //{
            //    foreach (object[] obj in objList)
            //    {
            //        ecfg = obj[0].ToString();
            //        if (ecfg.IndexOf(fileName) != -1)
            //        {
            //            return true;
            //        }
            //    }
            //}
            #endregion

            #region 模板表里面检索，模板背景
            objList = CommonAccess.sqlite.GetArray("select mbg from m_board;");
            ret = objList.Any(o => o[0].ToString().IndexOf(fileName) != -1);
            if (ret == true)
                return ret;

            //if (objList != null)
            //{
            //    foreach (object[] obj in objList)
            //    {
            //        ecfg = obj[0].ToString();
            //        if (ecfg.IndexOf(fileName) != -1)
            //        {
            //            return true;
            //        }
            //    }
            //}
            #endregion

            #region 模板表里面检索，模板背景音乐
            objList = CommonAccess.sqlite.GetArray("select msound from m_board;");
            ret = objList.Any(o => o[0].ToString().IndexOf(fileName) != -1);
            if (ret == true)
                return ret;

            //if (objList != null)
            //{
            //    foreach (object[] obj in objList)
            //    {
            //        ecfg = obj[0].ToString();
            //        if (ecfg.IndexOf(fileName) != -1)
            //        {
            //            return true;
            //        }
            //    }
            //}
            #endregion

            return false;
        }

        #endregion

       
    }
}
